﻿/*
 * 安特威D_user表操作类
 * zhouh@ZD_20160322
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DataControl;
using System.Data;
using System.Text;
using System.Collections;

namespace ATW.MT.BUS
{
    public class bus_D_user
    {
        DataAccess da = null;

        public bus_D_user()
        {
            da = new DataAccess();
        }

        #region 方法
        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="account">账号</param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public DataTable UserLogin(string account)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select * from d_user where account='" + account + "'");
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 用户账号是否存在
        /// </summary>
        /// <param name="account"></param>
        /// <returns></returns>
        public bool IsUserExist(string account)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"select * from d_user where account='" + account + "'");
            DataTable dt = da.ExecuteDataTable(sql.ToString());
            if (dt != null && dt.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 检查账号密码
        /// </summary>
        /// <param name="account"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool checkPwd(string account, string pwd)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from d_user where account='" + account + "' and pwd='" + pwd + "'");
            DataTable dt = da.ExecuteDataTable(sql.ToString());
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        public int UpdatePwd(string account, string newPwd)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"update d_user set pwd='" + newPwd + "' where account='" + account + "'");
            return da.ExcuteNoQuery(sql.ToString());
        }
        /// <summary>
        /// 新增用户
        /// </summary>
        /// <param name="account"></param>
        /// <param name="pwd"></param>
        /// <param name="level_class"></param>
        /// <returns></returns>
        public int UserAdd(string account, string pwd, string level_class)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"insert into D_User(account,pwd,level_class,rowstatus) values('{0}','{1}','{2}','1')",
                account, pwd, level_class);
            return da.ExcuteNoQuery(sql.ToString());
        }
        /// <summary>
        /// 获取用户所属用户组以及访问权限
        /// </summary>
        /// <param name="userid">用户id</param>
        /// <returns></returns>
        public DataTable getModuleList(string userid)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select u.level_class,m.* from d_user u 
	                left join d_user_level l on u.level_class=l.level_class
	                left join d_module m on l.moduleid=m.moduleid
	                where 1=1");
            if (userid != "")
            {
                sql.Append(" and u.userid='" + userid + "'");
            }
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 用户分页查询
        /// </summary>
        /// <param name="account">账号</param>
        /// <param name="status">状态，1正常，2删除</param>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public DataTable UserPagerQuery(string account, string status, int page, int pagesize)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select userid,account,level_class,rowstatus,create_time from d_user where 1=1");
            if (account != "")
            {
                sql.Append(" and account like '%" + account + "%'");
            }
            if (status != "")
            {
                sql.Append(" and rowstatus='" + status + "'");
            }
            return da.PageSize(sql.ToString(), page, pagesize, "userid");
        }
        /// <summary>
        /// 改变用户状态
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="status">1正常 2封禁</param>
        /// <returns></returns>
        public int UserChangeStatus(string userid, string status)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"update d_user set rowstatus='" + status + "' where userid='" + userid + "'");
            return da.ExcuteNoQuery(sql.ToString());
        }
        /// <summary>
        /// 重置密码
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int UserResetPwd(string userid)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"update D_user set pwd='888888' where userid='" + userid + "'");
            return da.ExcuteNoQuery(sql.ToString());
        }
        /// <summary>
        /// 查询用户组
        /// </summary>
        /// <returns></returns>
        public DataTable LevelQuery()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select distinct level_class from d_user_level");
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 用户组成员
        /// </summary>
        /// <param name="level"></param>
        /// <returns></returns>
        public DataTable LevelUser(string level)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select account,level_class,create_time from D_User where level_class='{0}'", level);
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 用户组模块
        /// </summary>
        /// <param name="level"></param>
        /// <returns></returns>
        public DataTable LevelModule(string level)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select distinct m.moduleid,m.module_name,m.module_level,isnull(module_url,'') module_url,m.father_id from D_Module m
                                left join  D_user_level ul  on ul.moduleid=m.moduleid
                                                            where 1=1");
            if (!string.IsNullOrEmpty(level))
            {
                sql.Append(" and  level_class ='" + level + "'");
            }
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 用户组模块（全部）
        /// </summary>
        /// <param name="level"></param>
        /// <returns></returns>
        public DataTable LevelModuleEx(string level)
        {
            StringBuilder sql = new StringBuilder();
            string s = "";
            if (level != "")
            {
                s = " and level_class='" + level + "'";
            }
            sql.AppendFormat(@"select m.moduleid,m.module_name,m.module_level,isnull(module_url,'') module_url,m.father_id,ISNULL(u.moduleid,'') flag,level_class from D_Module m left join 
                    (select moduleid,level_class from D_user_level where 1=1 {0} ) u on m.moduleid=u.moduleid", s);
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 用户组分页查询
        /// </summary>
        /// <param name="name">用户组名称</param>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public DataTable LevelPagerQuery(string name, int page, int pagesize)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"select s1.mnum,isnull(s2.unum,0) unum,s1.level_class from 
                            (select count(*) mnum,level_class from D_user_level group by level_class)s1 left join
                            (select COUNT(*) unum,level_class from D_User u group by level_class)s2 
                            on s1.level_class=s2.level_class where 1=1");
            if (name != "")
            {
                sql.Append(" and s1.level_class like '%" + name + "%'");
            }
            return da.PageSize(sql.ToString(), page, pagesize, "level_class");
        }
        /// <summary>
        /// 用户组名是否存在
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool IsGroupNameExist(string name)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"select * from d_user_level where level_class='" + name + "'");
            DataTable dt = da.ExecuteDataTable(sql.ToString());
            if (dt != null && dt.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 返回删除用户组语句
        /// </summary>
        /// <param name="levelClass"></param>
        /// <returns></returns>
        public string UserDelAuthor(string levelClass)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from d_user_level where level_class='" + levelClass + "'");
            return sql.ToString();
        }
        /// <summary>
        /// 返回设置用户组语句
        /// </summary>
        /// <param name="levelClass"></param>
        /// <param name="moduleId"></param>
        /// <returns></returns>
        public string UserSetAuthor(string levelClass, string moduleId)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("insert into D_user_level (level_class,moduleId) values('{0}','{1}')", levelClass, moduleId);
            return sql.ToString();
        }
        public bool ExAL(ArrayList al)
        {
            return da.ExecuteArrayList(al);
        }
        #endregion
    }
}